﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExcelDna.Integration;
using static ExcelDna.Integration.XlCall;
namespace ExcelUDF
{
    public partial class ExcelUDF
    {
        [ExcelFunction(Category = "文本处理_字符拼接", IsMacroType = true, Description = "字符串拼接函数，StringJoinRange：传入的需要写入目标单元格的数据区域，strSplit：用来分割的字符")]
        //AllowReference = true把传进来的EXCEL区域识别出来。
        public static string StringJoin(
           [ExcelArgument(Description = "输入要拼接的字符串区域", AllowReference = true)] object StringJoinRange,
           [ExcelArgument(Description = "输入分隔字符串")] string strSplit)
        {
            Excel(xlfVolatile, false);//取消易失性
            if (!(StringJoinRange is ExcelReference))
                return ExcelError.ExcelErrorName.ToString();
            //拿到EXCEL表的名称，工作薄[工作表] 的格式
            object targetSheetName = Excel(xlSheetNm, StringJoinRange);

            //拿到已使用区域的行列序号，从0开始
            int firstRow = (int)(double)Excel(xlfGetDocument, 9, targetSheetName) - 1;
            int lastRow = (int)(double)Excel(xlfGetDocument, 10, targetSheetName) - 1;
            int firstCol = (int)(double)Excel(xlfGetDocument, 11, targetSheetName) - 1;
            int lastCol = (int)(double)Excel(xlfGetDocument, 12, targetSheetName) - 1;
            //类型转换为ExcelReference，前面已经判断过是ExcelReference类型，不是的话程序会退出。
            ExcelReference targetRef = (ExcelReference)StringJoinRange;

            //拿到传入区域和已使用区域之间的交集
            ExcelReference usedTarget = new ExcelReference(
                    rowFirst: firstRow > targetRef.RowFirst ? firstRow : targetRef.RowFirst,
                    rowLast: lastRow < targetRef.RowLast ? lastRow : targetRef.RowLast,
                    columnFirst: firstCol > targetRef.ColumnFirst ? firstCol : targetRef.ColumnFirst,
                    columnLast: lastCol < targetRef.ColumnLast ? lastCol : targetRef.ColumnLast,
                    sheetId: targetRef.SheetId);
            //ExcelReference提取值内容放入object对象，再供下一步判断是只有一个单元格还是多个，多个时为object[,]
            object values = usedTarget.GetValue();


            if (!(values is object[,]))
            {
                if (values is ExcelEmpty)
                {
                    return "";
                }
                else
                {
                    return values.ToString();
                }

            }
            else
            {
                List<string> list = new List<string>();
                object[,] valuesArr = (object[,])values;

                for (int i = 0; i < valuesArr.GetLength(0); i++)
                {
                    for (int j = 0; j < valuesArr.GetLength(1); j++)
                    {
                        if (!(valuesArr.GetValue(i, j) is ExcelEmpty))
                        {
                            list.Add(valuesArr.GetValue(i, j).ToString());
                        }
                    }
                }

                return string.Join(strSplit, list);
            }

        }

        [ExcelFunction(Category = "文本处理_字符拼接", IsMacroType = true, Description = "字符串拼接函数，在查找的区域查找对应条件下的值，最终用分隔符拼接起来，类似系统函数SUMIF、COUNTIF")]
        public static string StringJoinIf(
    [ExcelArgument(Description = "查找的区域", AllowReference = true)] object lookupRange,
    [ExcelArgument(Description = "用于验证查找区域是否符合的条件")] string criteria,
    [ExcelArgument(Description = "要拼接的字符串区域", AllowReference = true)] object StringJoinRange,
    [ExcelArgument(Description = "输入分隔字符串")] string strSplit,
    [ExcelArgument(Description = "是否精确匹配，默认为否")] bool isExactMatch = false)
        {
            Excel(xlfVolatile, false);//取消易失性
            if (!(StringJoinRange is ExcelReference))
                return "错误！查找区域输入有误";

            if (!(StringJoinRange is ExcelReference))
                return "错误！拼接区域输入有误";

            object targetSheetName = Excel(xlSheetNm, lookupRange);

            int firstRow = (int)(double)Excel(xlfGetDocument, 9, targetSheetName) - 1;
            int lastRow = (int)(double)Excel(xlfGetDocument, 10, targetSheetName) - 1;
            int firstCol = (int)(double)Excel(xlfGetDocument, 11, targetSheetName) - 1;
            int lastCol = (int)(double)Excel(xlfGetDocument, 12, targetSheetName) - 1;

            ExcelReference lookupRangeRef = (ExcelReference)lookupRange;

            ExcelReference usedLookupRange = new ExcelReference(
                    rowFirst: firstRow > lookupRangeRef.RowFirst ? firstRow : lookupRangeRef.RowFirst,
                    rowLast: lastRow < lookupRangeRef.RowLast ? lastRow : lookupRangeRef.RowLast,
                    columnFirst: firstCol > lookupRangeRef.ColumnFirst ? firstCol : lookupRangeRef.ColumnFirst,
                    columnLast: lastCol < lookupRangeRef.ColumnLast ? lastCol : lookupRangeRef.ColumnLast,
                    sheetId: lookupRangeRef.SheetId);

            object lookupRangeValues = usedLookupRange.GetValue();

            //StringJoinRange区域转换
            ExcelReference StringJoinRangeRef = (ExcelReference)StringJoinRange;

            ExcelReference usedStringJoinRangeRange = new ExcelReference(
                    rowFirst: usedLookupRange.RowFirst,
                    rowLast: usedLookupRange.RowLast,
                    columnFirst: firstCol > StringJoinRangeRef.ColumnFirst ? firstCol : StringJoinRangeRef.ColumnFirst,
                    columnLast: lastCol < StringJoinRangeRef.ColumnLast ? lastCol : StringJoinRangeRef.ColumnLast,
                    sheetId: StringJoinRangeRef.SheetId);

            object StringJoinRangeValues = usedStringJoinRangeRange.GetValue();


            if (!(lookupRangeValues is object[,]))
            {
                if (lookupRangeValues is ExcelEmpty)
                {
                    return "";
                }
                else
                {
                    return StringJoinRange.ToString();
                }

            }
            else
            {
                List<string> list = new List<string>();

                object[,] lookupRangeValuesArr = (object[,])lookupRangeValues;
                object[,] StringJoinRangeValuesArr = (object[,])StringJoinRangeValues;

                for (int i = 0; i < lookupRangeValuesArr.GetLength(0); i++)
                {
                    if (isExactMatch)
                    {
                        if (lookupRangeValuesArr.GetValue(i, 0).ToString() == criteria)
                        {
                            list.Add(StringJoinRangeValuesArr.GetValue(i, 0).ToString());
                        }

                    }
                    else
                    {
                        if (lookupRangeValuesArr.GetValue(i, 0).ToString().Contains(criteria))
                        {
                            list.Add(StringJoinRangeValuesArr.GetValue(i, 0).ToString());
                        }
                    }
                }

                return string.Join(strSplit, list);

            }

        }

    }
}
